import win32com.client
import pythoncom
import time
from obj.dt import DT
from optparse import OptionParser

openxl=True
wb=None

def table(header,table,sumlist=[]):
    global openxl, wb
    if openxl:
        xl = win32com.client.Dispatch ("Excel.Application")
        xl.Visible = 1
        wb = xl.Workbooks.Add()
        openxl=False
    wb.Sheets.Add()
    xls = wb.ActiveSheet

    t=[header]
    t.extend(table)

    for r,l in enumerate(t):
        for c,v in enumerate(l):
            vv=None
            if v==None or isinstance(v,float) or isinstance(v,str):
                vv=v
            elif isinstance(v,DT):
                vv=pythoncom.MakeTime(time.mktime(v.dt.timetuple()))
            elif isinstance(v,list):
                vv='\n'.join([str(v1) for v1 in v])
            else:
                vv=str(v)
            xls.Cells(r+1,c+1).Value=vv

    if len(t)>1:
        r=len(t)
    for c,v in enumerate(header):
        if v in sumlist:
            xls.Cells(r+1,c+1).Formula = "=SUM(%s2:%s%d)"%(chr(ord('A')+c),chr(ord('A')+c),r)

    fitsheet(xls,len(header),len(table)+1+1)
    #wb.Close(SaveChanges=0)
    #xl.Quit()
    #openxl=True


def fitsheet(sh,w,h):
    for i,c in enumerate(sh.Columns):
        if i>=w: break
        c.EntireColumn.AutoFit()
    for i,r in enumerate(sh.Rows):
        if i>=h: break
        r.EntireRow.AutoFit()

def xlsprintsetup(fname):
    """Fix excel file so all columns will be auto fitted to the correct width
and set the print setup to be landscape with header/footer showing the sheet
name and a grid"""
    xl = win32com.client.Dispatch ("Excel.Application")
    wb = xl.Workbooks.Open (fname)
    ws=wb.Sheets
    for sh in ws:
        for c in sh.Columns:
            c.EntireColumn.AutoFit()
        ps=sh.PageSetup
        ps.CenterHeader = "&A"
        ps.CenterFooter = "&A"
        ps.PrintGridlines = 1 # True
        ps.Orientation = 2 # xLandscape
    wb.Save()
    wb.Close()

def main():
    parser = OptionParser(__doc__)
#    parser.add_option("-f", "--file", dest="filename",
#                      help="Excel file to be modified")
    (options, args) = parser.parse_args()
    if len(args) != 1:
        parser.error("Missing file name")
    try:
        xlsprintsetup(args[0])
    except:
        print "Failed to fix excel file"
        sys.exit(1)

if __name__ == "__main__":
    main()
